using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace EM.utility
{
    public class DataSrv
    {

        SqlConnection Cnn;
        string sConnectionString;
        int SQLCommandTimeout = 120;

        public DataSrv()
        {
            sConnectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
            SQLCommandTimeout = Convert.ToInt16(System.Configuration.ConfigurationManager.AppSettings["SQLCommandTimeout"].ToString());
        }

        public void OpenCnn()
        {
            if (Cnn == null)
            {
                Cnn = new SqlConnection(sConnectionString);
            }

            if (Cnn.State != System.Data.ConnectionState.Open)
            {
                Cnn.Open();
            }
        }

        public void CloseCnn()
        {
            Cnn.Close();
            Cnn.Dispose();
            Cnn = null;
        }

        public System.Data.SqlClient.SqlCommand CreateSqlCommand(string sSQL)
        {
            SqlCommand oSqlCmd = null;

            try
            {
                this.OpenCnn();
                oSqlCmd = new System.Data.SqlClient.SqlCommand(sSQL, Cnn);
                oSqlCmd.CommandTimeout = SQLCommandTimeout;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return oSqlCmd;
        }

        public System.Data.SqlClient.SqlDataAdapter CreateSqlAdapter(System.Data.SqlClient.SqlCommand SqlCMD)
        {
            SqlDataAdapter oDa = null;
            try
            {
                this.OpenCnn();
                SqlCMD.Connection = Cnn;
                SqlCMD.CommandTimeout = SQLCommandTimeout;
                oDa = new SqlDataAdapter(SqlCMD);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return oDa;
        }

        public System.Data.SqlClient.SqlDataAdapter CreateSqlAdapter(string sSQL)
        {
            SqlDataAdapter oDa = null;
            try
            {
                this.OpenCnn();
                oDa = new SqlDataAdapter(sSQL, Cnn);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return oDa;
        }

        public System.Data.DataTable CreateDataTable(string sSQL, string sTableName)
        {
            DataTable Dt = new DataTable(sTableName);
            try
            {
                this.CreateSqlAdapter(CreateSqlCommand(sSQL)).Fill(Dt);
                this.CloseCnn();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return Dt;
        }

        public System.Data.DataTable CreateDataTable(System.Data.SqlClient.SqlCommand SqlCMD, string sTableName)
        {
            DataTable Dt = new DataTable(sTableName);
            SqlCMD.CommandTimeout = SQLCommandTimeout;
            try
            {
                this.CreateSqlAdapter(SqlCMD).Fill(Dt);
                this.CloseCnn();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return Dt;
        }

        public System.Data.DataTable CreateDataTable2(System.Data.SqlClient.SqlCommand SqlCMD, string sTableName)
        {
            DataTable Dt = new DataTable(sTableName);
            SqlCMD.CommandTimeout = SQLCommandTimeout;
            try
            {
                DataColumn Clm = new DataColumn("ROWNUM", System.Type.GetType("System.Int32"));
                Clm.AutoIncrement = true;
                Clm.AutoIncrementSeed = 1;
                Clm.AutoIncrementStep = 1;
                Clm.ReadOnly = true;
                Dt.Columns.Add(Clm);

                this.CreateSqlAdapter(SqlCMD).Fill(Dt);
                this.CloseCnn();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return Dt;
        }

        public System.Data.DataSet CreateDataSet(string sSQL)
        {
            DataSet Ds = new DataSet();
            try
            {
                this.CreateSqlAdapter(CreateSqlCommand(sSQL)).Fill(Ds);
                this.CloseCnn();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return Ds;
        }

        public System.Data.DataSet CreateDataSet(System.Data.SqlClient.SqlCommand SqlCMD)
        {
            DataSet Ds = new DataSet();
            SqlCMD.CommandTimeout = SQLCommandTimeout;
            try
            {
                this.CreateSqlAdapter(SqlCMD).Fill(Ds);
                this.CloseCnn();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return Ds;
        }

        public void FnExecuteNonQuery(string sSQL)
        {
            SqlCommand cmd = this.CreateSqlCommand(sSQL);
            cmd.Connection = Cnn;
            SqlTransaction trans = Cnn.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new Exception(e.Message);
            }
            finally
            {
                this.CloseCnn();
            }
        }

        public void FnExecuteNonQuery(SqlCommand cmd)
        {
            this.OpenCnn();
            cmd.Connection = Cnn;
            cmd.CommandTimeout = SQLCommandTimeout;
            SqlTransaction trans = Cnn.BeginTransaction();
            cmd.Transaction = trans;

            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new Exception(e.Message);
            }
            finally
            {
                this.CloseCnn();
            }
        }

        public string FnExecuteScalar(string sSQL)
        {
            object _return = null;

            SqlCommand cmd = this.CreateSqlCommand(sSQL);
            cmd.Connection = Cnn;
            SqlTransaction trans = Cnn.BeginTransaction();
            cmd.Transaction = trans;

            try
            {
                _return = cmd.ExecuteScalar();
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new Exception(e.Message);
            }
            finally
            {
                this.CloseCnn();
            }
            _return = Convert.ToString(_return);
            if (_return == null) { _return = ""; }

            return Convert.ToString(_return);
        }

        public string FnExecuteScalar(SqlCommand cmd)
        {
            string _return = null;

            this.OpenCnn();
            cmd.Connection = Cnn;
            cmd.CommandTimeout = SQLCommandTimeout;
            SqlTransaction trans = Cnn.BeginTransaction();
            cmd.Transaction = trans;

            try
            {
                _return = cmd.ExecuteScalar().ToString();
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new Exception(e.Message);
            }
            finally
            {
                this.CloseCnn();
            }

            return _return;
        }

        public SqlDataSource CreateDataSource(string sSQL)
        {
            SqlDataSource Ds = new SqlDataSource();
            Ds.ConnectionString = sConnectionString;
            Ds.ProviderName = "System.Data.SqlClient";
            Ds.SelectCommand = sSQL;
            //Ds.DataSourceMode = "SqlDataReader";

            return Ds;
        }

        public int getCounterVal(string strColumn)
        {
            string sSQL;
            int CounterVal = 0;

            sSQL =  " DECLARE @COUNTERVALUE BIGINT";
            sSQL += " EXEC spGetCounter '"+strColumn.ToString()+"' , @COUNTERVALUE output ";
            sSQL += " SELECT @COUNTERVALUE";
            DataTable Dt = CreateDataTable(sSQL,"Dt");

            if (Dt.Rows[0][0].ToString() != "")
            {
                CounterVal = Convert.ToInt32(Dt.Rows[0][0].ToString());
            }
            return CounterVal;
        }

        public string getGlobalDefaultVal(string GlobalCode)
        {
            string sSQL;
            sSQL = "SELECT ISNULL(GLOBALVALUE,'') GLOBALVALUE FROM TBLGLOBALDEFAULT  WHERE GLOBALCODE = '" + GlobalCode + "';";
            DataTable Dt = CreateDataTable(sSQL,"");

            if (Dt.Rows.Count > 0)
            {
                return Dt.Rows[0][0].ToString();
            }
            else
            {
                return "";
            }

        }

        public System.Data.SqlClient.SqlDataReader CreateSqlDataReader(string sSQL)
        {
            SqlCommand myCommand = null;
            SqlDataReader myReader = null;

            try
            {
                if (myReader != null && !myReader.IsClosed)
                { myReader.Dispose(); }
                myCommand = this.CreateSqlCommand(sSQL);
                myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                //myReader.Read();
                //this.CloseCnn();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return myReader;
        }

        public System.Data.SqlClient.SqlDataReader CreateSqlDataReader(SqlCommand Command)
        {
            SqlDataReader myReader = null;

            try
            {
                myReader = Command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                myReader.Read();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return myReader;
        }
    }
}
